﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
using Microsoft.ApplicationBlocks.Data;

namespace AirlineCommon
{
    public class EconomyClass:TravelClass 
    {
        //Trả về số tiền vé EconomyClassFare.Economy theo SectorID
        public override float GetFareAmount()
        {
            string strCONN = DAL.DatabaseProvider.AirlineConnectionString;
            string sql = "Select EconomyClassFare from  Sector where SectorID=@SectorID";
            float _EconomyClassFare = 0;
            try
            {
                SqlParameter pSecID = new SqlParameter("@SectorID", SectorID);
                DataSet ds = SqlHelper.ExecuteDataset(strCONN, CommandType.Text, sql, pSecID);
                if (ds.Tables[0].Rows.Count > 0)
                    _EconomyClassFare = float.Parse(ds.Tables[0].Rows[0][0].ToString());
                else
                    _EconomyClassFare = 0;
                return _EconomyClassFare;
            }
            catch (Exception ex)
            {
                //Log Exception (will be provided in next release)
                AppLogger.Error("Error Executing in Get Fare Amount", ex, "EconomyClass", "GetFareAmount");
                throw;
            }
        }
        //Trả về ScheduledFlights.EconomyClassSeatsAvailable cho loại vé Economy
        public override int GetSeatAvailable(string flightNo, DateTime flightdate)
        {
            string strCONN = DAL.DatabaseProvider.AirlineConnectionString;
            string sql = "Select EconomyClassSeatsAvailable from  ScheduledFlights where FlightNo=@FlightNo And";
            sql += " Day(FlightDate)=@FlightDay And Month(FlightDate)=@FlightMonth And Year(FlightDate)=@FlightYear";
            int _EconomyClassSeatsAvailable = 0;
            try
            {
                SqlParameter pFlightNo = new SqlParameter("@FlightNo", flightNo);
                SqlParameter pFlightDay = new SqlParameter("@FlightDay", flightdate.Day);
                SqlParameter pFlightMonth = new SqlParameter("@FlightMonth", flightdate.Month);
                SqlParameter pFlightYear = new SqlParameter("@FlightYear", flightdate.Year);
                DataSet ds = SqlHelper.ExecuteDataset(strCONN, CommandType.Text, sql, pFlightNo,pFlightDay,pFlightMonth,pFlightYear);
                if (ds.Tables[0].Rows.Count > 0)
                    _EconomyClassSeatsAvailable = int.Parse(ds.Tables[0].Rows[0][0].ToString());
                else
                    _EconomyClassSeatsAvailable = 0;
                return _EconomyClassSeatsAvailable;
            }
            catch (Exception ex)
            {
                //Log Exception (will be provided in next release)
                AppLogger.Error("Error Executing in Get Economy Seat Available", ex, "EconomyClass", "GetSeatAvailable");
                throw;
            }
        }
        //Giảm số vé ScheduledFlights.EconomyClassSeatsAvailable -1
        public override void UpdateClassSeat(string flightNo, DateTime FlightDate)
        {
            SqlConnection conn = new SqlConnection(DAL.DatabaseProvider.AirlineConnectionString);
            string SQL = "Update ScheduledFlights Set EconomyClassSeatsAvailable=EconomyClassSeatsAvailable-1 ";
            SQL += "Where FlightNo=@FlightNo And Day(FlightDate)=@DayFlight And Month(FlightDate)=@MonthFlight And Year(FlightDate)=@YearFlight";

            SqlParameter pFlightNo = new SqlParameter("@FlightNo", flightNo);
            SqlParameter pDayFlight = new SqlParameter("@DayFlight", FlightDate.Day);
            SqlParameter pMonthFlight = new SqlParameter("@MonthFlight", FlightDate.Month);
            SqlParameter pYearFlight = new SqlParameter("@YearFlight", FlightDate.Year);

            int HowManyRows = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, SQL, pFlightNo,
                    pDayFlight,
                    pMonthFlight,
                    pYearFlight);
            if (HowManyRows != 1)
                throw new Exception("Update ScheduledFlights fail.");
            // sqlComm.Dispose();
            conn.Close();
            return;
        }
        //Giảm số vé ScheduledFlights.EconomyClassSeatsAvailable -1
        public override int UpdateClassSeatForReservation(SqlTransaction trans, string flightNo, DateTime FlightDate)
        {
            try
            {
                //SqlConnection conn = new SqlConnection(DAL.DatabaseProvider.AirlineConnectionString);
                string SQL = "Update ScheduledFlights Set EconomyClassSeatsAvailable=EconomyClassSeatsAvailable-1 ";
                SQL += "Where FlightNo=@FlightNo And Day(FlightDate)=@DayFlight And Month(FlightDate)=@MonthFlight And Year(FlightDate)=@YearFlight";

                SqlParameter pFlightNo = new SqlParameter("@FlightNo", flightNo);
                SqlParameter pDayFlight = new SqlParameter("@DayFlight", FlightDate.Day);
                SqlParameter pMonthFlight = new SqlParameter("@MonthFlight", FlightDate.Month);
                SqlParameter pYearFlight = new SqlParameter("@YearFlight", FlightDate.Year);

                int HowManyRows = SqlHelper.ExecuteNonQuery(trans, CommandType.Text, SQL, pFlightNo,
                        pDayFlight,
                        pMonthFlight,
                        pYearFlight);
                return HowManyRows;
            }
            catch
            {
                return -1;
            }
        }
        //Tăng số vé ScheduledFlights.EconomyClassSeatsAvailable+1
        public override int UpdateClassSeatForCancellation(SqlTransaction trans, string flightNo, DateTime FlightDate)
        {
            
                try
                {
                    //SqlConnection conn = new SqlConnection(DAL.DatabaseProvider.AirlineConnectionString);
                    string SQL = "Update ScheduledFlights Set EconomyClassSeatsAvailable=EconomyClassSeatsAvailable+1 ";
                    SQL += "Where FlightNo=@FlightNo And Day(FlightDate)=@DayFlight And Month(FlightDate)=@MonthFlight And Year(FlightDate)=@YearFlight";

                    SqlParameter pFlightNo = new SqlParameter("@FlightNo", flightNo);
                    SqlParameter pDayFlight = new SqlParameter("@DayFlight", FlightDate.Day);
                    SqlParameter pMonthFlight = new SqlParameter("@MonthFlight", FlightDate.Month);
                    SqlParameter pYearFlight = new SqlParameter("@YearFlight", FlightDate.Year);

                    int HowManyRows = SqlHelper.ExecuteNonQuery(trans, CommandType.Text, SQL, pFlightNo,
                            pDayFlight,
                            pMonthFlight,
                            pYearFlight);
                    return HowManyRows;
                }
                catch
                {
                    return -1;
                }
           
        }
        //Lấy ra Passengers.PNRNo đầu tiên trong hàng đợi
        public string GetPNRPassengerOverBook(string FlightNo, DateTime TravelDate)
        {
            string sQry = "Select min(PNRNo) from Passengers ";
            sQry += "Where FlightNo='" + FlightNo + "' and ";
            sQry += "day(TravelDate)= @TravelDay and Month(TravelDate)=@TravelMonth ";
            sQry += "and year(TravelDate)=@TravelYear ";
            sQry += "and Status='OverBook'";
            string strConn = DAL.DatabaseProvider.AirlineConnectionString;
            SqlConnection conn = new SqlConnection(strConn);
            SqlParameter pTravelDay = new SqlParameter("@TravelDay", TravelDate.Day);
            SqlParameter pTravelMonth = new SqlParameter("@TravelMonth", TravelDate.Month);
            SqlParameter pTravelYear = new SqlParameter("@TravelYear", TravelDate.Year);

            DataSet ds = SqlHelper.ExecuteDataset(conn, CommandType.Text, sQry, pTravelDay, pTravelMonth, pTravelYear);

            if (ds.Tables[0].Rows.Count == 0) return string.Empty;
            DataRow dr = ds.Tables[0].Rows[0];
            return dr[0].ToString();
            //return string.Empty;
        }
      
    }
}
